﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;

/// <summary>
/// Summary description for Truycapdulieu
/// </summary>
public class Truycapdulieu
{
	//
	// TODO: Add constructor logic here
	//

            private static string StrKetnoi = @"Data Source=JOSEPH-PC\SQLEXPRESS;Initial Catalog=DiaDiem; Integrated Security=True";
            public static SqlConnection CN;
            public static string ChuoiKetnoi()
            {
                return StrKetnoi;
            }
            public static void ThietlapKetnoi()
            {
                try
                {
                    CN = new SqlConnection(StrKetnoi);
                    CN.Open();

                }
                catch
                {

                }
            }
            public static void HuyKetnoi()
            {
                CN.Close();
            }

            ////Lấy dữ liệu từ bảng
            public DataTable TaoBang(string sql)
            {

                ThietlapKetnoi();
                SqlDataAdapter Adapter = new SqlDataAdapter(sql,CN);
                DataTable DTable = new DataTable();
                Adapter.Fill(DTable);
                Adapter.Update(DTable);
                return DTable;

            }
            //Ham thuc hien lenh executenonquery
            public void ExcuteNonQuery(string sql)
            {
                //Ket noi Database
                ThietlapKetnoi();
                //Thực hiện insert
                SqlCommand cmd = new SqlCommand(sql, CN);
                cmd.ExecuteNonQuery();
                //Hủy kết nối Database
                HuyKetnoi();
                cmd.Dispose();
            }
            public void Check(string Quan, string ThanhPho, string DiaChi)
            {
                ThietlapKetnoi();
                SqlDataAdapter adap = new SqlDataAdapter("Select * from TBL_DiaDiem", CN);
                DataSet data = new DataSet();
                adap.Fill(data);
                foreach (DataRow row in data.Tables[0].Rows)
                {
                    if (row[3].ToString() == DiaChi && row[4].ToString() == Quan && row[5].ToString() == ThanhPho)
                    {
                        SqlCommand command = new SqlCommand("UPDATE [TBL_DiaDiem] SET [TrangThai] = 0 WHERE ID = " + row[0], CN);
                        command.ExecuteNonQuery();

                    }
                }
                HuyKetnoi();


            }
            public DataSet Lng(string ID)//Kinh do
            {
                ThietlapKetnoi();
                SqlDataAdapter adap = new SqlDataAdapter("Select Lng from TBL_DiaDiem where ID='"+ID+"'", CN);
                DataSet data = new DataSet();
                adap.Fill(data);
                adap.Update(data);
                return data;
            }


}